package com.biz.jdbc;

import java.io.ByteArrayInputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.eyeieye.melody.util.DateUtil;
import com.util.ConnDatabase;
import com.util.DbUtil;
import com.util.ZLibUtils;


/**
 * @author liuchao
 * @version $Id: ProductDao.java,v 0.1 2012-9-15 ����12:34:48 liuchao Exp $
 */
public class ProductDao {
    

    public static final String SQL_INSERT = String.format(
        "insert into %s (%s,%s,%s) values (?,?,?)"  ,
        "props",
        "pid",
        "pname",
        "cid"
          );
    
    
    ConnDatabase connDatabase = new ConnDatabase();
    Statement stmt = null;
    ResultSet rs = null;
    PreparedStatement pstmt = null;
    
    //创建表
    public void createTable( String   tableName  ) throws SQLException{
        
        String create =  "CREATE TABLE "+tableName+ "( " +
                "`id` bigint(20) NOT NULL auto_increment,"+
                "`product_ID` bigint(20) default NULL,"+
                "`cat_id` int(11) default NULL,"+
                "`price` double default NULL,"+
                "`Pic_url` varchar(200) collate utf8_unicode_ci default NULL,"+
                "`Title` varchar(200) collate utf8_unicode_ci default NULL,"+
                "`Description` blob  default NULL,"+
                "`Nick_name` varchar(60) collate utf8_unicode_ci default NULL,"+
                "`from_site` varchar(60) collate utf8_unicode_ci default NULL,"+
                "`Detail_url` varchar(400) collate utf8_unicode_ci default NULL,"+
                "`Props_name` blob default NULL,"+
                "`props` blob   default NULL,"+
                "`create_time` varchar(20) collate utf8_unicode_ci default NULL,"+
                " `global_stock_type` varchar(1) collate utf8_unicode_ci default NULL,"+
                " `num` int(11) default NULL,"+
                " `item_img_url` blob    default NULL,"+
                " `volume` int(11) default NULL,"+
                " `Keywords` varchar(60) collate utf8_unicode_ci default NULL,"+
                " `modified_time` varchar(20) collate utf8_unicode_ci default NULL,"+
                " `States` varchar(10) collate utf8_unicode_ci default NULL,"+
                " PRIMARY KEY  (`id`)"+
                  "   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; " ;
                  
        Connection conn = DbUtil.getConnection();
        ResultSet rs2=null;
        PreparedStatement pstmt1 = null;
        try{
            pstmt1 = conn.prepareStatement(create);
            System.out.println("table sql :"+create);
            pstmt1.execute();
            pstmt1.close();
            pstmt1=null;
        }catch(Exception e)
        {
            e.printStackTrace();
        }
        finally{
            connDatabase.CloseConnection(conn, null, rs2);
        }
    }
    
    public static void main(String args[]) throws SQLException{
        ProductDao dao = new ProductDao();
//        Product product = new Product();
//        product.setProductId(11L);
//        String inputStr = "����";  
//        byte[] data = ZLibUtils.compress(inputStr.getBytes());  
//        product.setDesction(data);
//        product.setProductId(1121L);
//        dao.insert(1L, product);
        
        for(int i=0;i<100;i++){
            dao.createTable("product_"+i);
        }
        //��ѯ����
       Product product = dao.getByTabName("product_0", 12934808800L);
        System.out.println("iid====="+product.getProductId());
        
        byte[] output = ZLibUtils.decompress( product.getDesction() );
        System.out.println("desc��ѹ֮��====="+new String(output)   );
    }
    
    public Product getByIId( Long iid)throws SQLException{
        Connection conn = DbUtil.getConnection();
        Product product = null;
        String tabName = "product_"+iid%100;
        try{
            String sql_select = "select * from "+tabName+" where product_ID="+iid;
            stmt = conn.createStatement();
            System.out.println("sql:"+sql_select);
            rs = stmt.executeQuery(sql_select);
            while(rs.next()){
                product = new Product();
                product.setProductId( rs.getLong("product_ID"));
            }
            return product;
        }catch(Exception e) {
            e.printStackTrace();
        }
        finally{
            connDatabase.CloseConnection(conn, stmt, rs);
        }
        return null;
    }
    
    public List<Product> getByStatus( String tabName,String states ,int pageSize)throws SQLException{
        Connection conn = DbUtil.getConnection();
        try{
            String sql_select = "";
            Product product = null;
            List<Product> productList = new ArrayList<Product>();
            if( null == states ){
                sql_select = "select * from "+tabName+" where  product_id is not null and states is null limit "+pageSize+" ";
            } else {
                sql_select = "select * from "+tabName+" where  product_id is not null and states='"+states+"' limit "+pageSize+" ";
            }
            System.out.println("sql====="+sql_select    );
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql_select);
            while(rs.next()){
                product = new Product();
                product.setProductId( rs.getLong("product_ID"));
                productList.add(product);
            }
            return productList;
        }catch(Exception e) {
            e.printStackTrace();
        }
        finally{
            connDatabase.CloseConnection(conn, stmt, rs);
        }
        return null;
    }
    
    public void updateByIid( Product product )throws SQLException{
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt1 = null;
        try{
            String tabName = "product_"+product.getProductId()%100;
            String sql_update = "update     "+tabName+" set Description =?,from_site=?,Detail_url=?,Props_name=?,props=?,create_time=?," +
            		"num=?,item_img_url=?,states=? where product_ID="+product.getProductId();
            System.out.println("sql_update:"+sql_update);
            pstmt1 = conn.prepareStatement(sql_update);
            //描述
            ByteArrayInputStream byteDescInputStream = new ByteArrayInputStream(product.getDesction());
            pstmt1.setBinaryStream(1, byteDescInputStream, product.getDesction().length); 
            //来源网站
            pstmt1.setString(2, "www.taobao.com");
            //Detail_url
            pstmt1.setString(3, product.getDetailUrl());
            //Props_name
            ByteArrayInputStream bytePropsNameInputStream = new ByteArrayInputStream(product.getPropsName());
            pstmt1.setBinaryStream(4, bytePropsNameInputStream, product.getPropsName().length); 
            //props
            ByteArrayInputStream bytePropsInputStream = new ByteArrayInputStream(product.getProps());
            pstmt1.setBinaryStream(5, bytePropsInputStream, product.getProps().length); 
            // create_time
            pstmt1.setString(6, DateUtil.getDate(new Date()));
            // num
            pstmt1.setLong(7, product.getNum());
            // item_img_url
            ByteArrayInputStream byteImgUrlInputStream = new ByteArrayInputStream(product.getItemImgUrl());
            pstmt1.setBinaryStream(8, byteImgUrlInputStream, product.getItemImgUrl().length); 
            // states
            pstmt1.setString(9, "0111");
            pstmt1.execute();
            pstmt1.close();
            pstmt1=null;
        }catch(Exception e) {
            e.printStackTrace();
        }
        finally{
            connDatabase.CloseConnection(conn, stmt, rs);
        }
    }
    
    public int deleteByIid( Long iid) throws SQLException{
        Connection conn = DbUtil.getConnection();
        try{
            String tabName = "product_"+iid%100;
            String sql_delete = "delete from "+tabName+" where product_id="+iid;
            System.out.println("sql_delete:"+sql_delete);
            stmt = conn.createStatement();
            return stmt.executeUpdate(sql_delete);
        }catch(Exception e) {
            e.printStackTrace();
        }
        finally{
            connDatabase.CloseConnection(conn, stmt, rs);
        }
        return 0;
    }
    
    public Product getByTabName(String tabName,Long iid)throws SQLException{
        Connection conn = DbUtil.getConnection();
        Product product = null;
        try{
            String sql_select = "select * from "+tabName+" where product_ID="+iid;
            stmt = conn.createStatement();
            System.out.println("sql:"+sql_select);
            rs = stmt.executeQuery(sql_select);
            while(rs.next()){
                product = new Product();
                product.setProductId( rs.getLong("product_ID"));
                Blob bb = rs.getBlob("Description");
                byte[] b = bb.getBytes(1, (int)bb.length());
                product.setDesction(b);
                System.out.println("desc byte�ֽ�:"+b.length);
                
            }
            return product;
        }catch(Exception e) {
            e.printStackTrace();
        }
        finally{
            connDatabase.CloseConnection(conn, stmt, rs);
        }
        return null;
    }
    
    //
    public void insertBySearchAPI (Long table,Product product)throws SQLException{
        String sql_insert = String.format(
            "insert into product_"+table+ " (%s,%s,%s,%s,%s,%s,%s) values (?,?,?,?,?,?,?)"  ,
            "product_ID",
            "cat_id",
            "price",
            "Pic_url",
            "Title",
            "Nick_name",
            "volume"
              );
        
        Connection conn = DbUtil.getConnection();
        ResultSet rs2=null;
        PreparedStatement pstmt1 = null;
        try{
            pstmt1 = conn.prepareStatement(sql_insert);
            System.out.println("sql:"+sql_insert);
            pstmt1.setLong(1, product.getProductId());
            pstmt1.setLong(2, product.getCatId());
            pstmt1.setDouble(3, product.getPrice());
            pstmt1.setString(4, product.getPicUrl());
            pstmt1.setString(5, product.getTitle());
            pstmt1.setString(6, product.getNickName() ); 
            pstmt1.setInt(7, product.getVolume());
            pstmt1.execute();
            pstmt1.close();
            pstmt1=null;
        }catch(Exception e) {
            e.printStackTrace();
        }
        finally{
            connDatabase.CloseConnection(conn, null, rs2);
        }
    }
    
    //全部字段插入
    public void insert(Long table,Product product) throws SQLException{

         String sql_insert = String.format(
            "insert into product_"+table+ " (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"  ,
            "product_ID",
            "cat_id",
            "price",
            "Pic_url",
            "Title",
            "Description",
            "Nick_name",
            "from_site",
            "Detail_url",
            "Props_name",
            "props",
            "create_time",
            "global_stock_type",
            "num",
            "item_img_url",
            "volume",
            "Keywords",
            "modified_time",
            "States"
              );
//        String sql_insert = String.format(
//            "insert into product_"+table+ " (%s,%s) values (?,?)"  ,
//            "product_ID",
//            "Description"
//              );
        Connection conn = DbUtil.getConnection();
        ResultSet rs2=null;
        PreparedStatement pstmt1 = null;
        try{
            pstmt1 = conn.prepareStatement(sql_insert);
            System.out.println("sql:"+sql_insert);
            pstmt1.setLong(1, product.getProductId());
            pstmt1.setLong(2, product.getCatId());
            pstmt1.setDouble(3, product.getPrice());
            pstmt1.setString(4, product.getPicUrl());
            pstmt1.setString(5, product.getTitle());
            ByteArrayInputStream byteDescInputStream = new ByteArrayInputStream(product.getDesction());
            pstmt1.setBinaryStream(6, byteDescInputStream, product.getDesction().length); 
            pstmt1.setString(7, product.getNickName());
            pstmt1.setString(8, "www.taobao.com");
            pstmt1.setString(9, product.getDetailUrl());
            ByteArrayInputStream bytePropsInputStream = new ByteArrayInputStream(product.getProps());
            pstmt1.setBinaryStream(10, bytePropsInputStream, product.getProps().length);
            ByteArrayInputStream bytePropsNameInputStream = new ByteArrayInputStream(product.getPropsName());
            pstmt1.setBinaryStream(11,bytePropsNameInputStream,  product.getPropsName().length);
            pstmt1.setString(12, "2012-09-18:23:19");
            pstmt1.setString(13, product.getGlobalStockType());
            pstmt1.setLong(14, product.getNum());
            
            ByteArrayInputStream byteImgUrlsInputStream = new ByteArrayInputStream(product.getItemImgUrl());
            pstmt1.setBinaryStream(15, byteImgUrlsInputStream,  product.getItemImgUrl().length);
            pstmt1.setLong(16, product.getVolume());
            pstmt1.setString(17, product.getKeywords());
            pstmt1.setString(18, "");
            pstmt1.setLong(19, 0000L);
            pstmt1.execute();
            pstmt1.close();
            pstmt1=null;
        }catch(Exception e) {
            e.printStackTrace();
        }
        finally{
            connDatabase.CloseConnection(conn, null, rs2);
        }
    }
}
